package com.kpmg.datalake.common.utils;

import com.kpmg.datalake.common.exception.CustomException;
import jxl.Cell;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel样式工具类
 * @author user
 */
public class KPMGStyleExcelUtil {
	
	private KPMGStyleExcelUtil() {
		
	}
	
	private static final Logger logger = LoggerFactory.getLogger(KPMGStyleExcelUtil.class);

    private static jxl.format.CellFormat numFormat = null;

    private static void releaseResources(InputStream template, OutputStream out, WritableWorkbook wbook,
                                         Workbook templateBook) throws IOException, WriteException {
        if (templateBook != null) {
            templateBook.close();
        }
        if (wbook != null) {
            wbook.close();
        }
        if (template != null) {
            template.close();
        }
        if (out != null) {
            out.close();
        }
    }

	/**
	 * 数据导出
     * @param template template
     * @param filtrate filtrate
     * @param sheetSize sheetSize
     * @param list list
     * @param sheetName sheetName
     * @param out out
     * @throws WriteException WriteException
     * @throws IOException IOException
	 */
    public static void exportExcel(InputStream template, Map<String, String> filtrate, int sheetSize,
                                   List<Map<String,String>> list, String sheetName, OutputStream out) throws WriteException, IOException {
		WritableWorkbook wbook = null;
		Workbook templateBook = null;
		try {
            templateBook = Workbook.getWorkbook(template);
			wbook = Workbook.createWorkbook(out, templateBook);
			//设置查询条件（设置公共头）
			WritableSheet baseSheet = wbook.getSheet(0);
			for(int i=1;i<8;i++) {
				String title = baseSheet.getCell(0, i).getContents();
				title = title.contains(":")?title.split(":")[0]:title;
				if(filtrate.containsKey(title)) {
					Label label = new Label(1, i, filtrate.get(title));
					baseSheet.addCell(label);
				}
			}

			sheetSize = (sheetSize<1||sheetSize>65525)?65525:sheetSize;
			double sheetNum = Math.ceil(list.size() / Double.valueOf(String.valueOf(sheetSize)));
			for (int i = 0; i < sheetNum; i++) {
				if(i + 1 < sheetNum){
					wbook.copySheet(i, sheetName, i+1);
				}
				WritableSheet sheet = wbook.getSheet(i);
				//设置宽度自适应
				sheet.setName(sheetName+i);
				//获取最大索引和最后一个索引
				int maxIndex = list.size()-1;
				int overIndex = (i+1)*sheetSize-1;
				// 获取开始索引和结束索引
				int firstIndex = i * sheetSize;
				int lastIndex = overIndex > maxIndex ? maxIndex : overIndex;

                exportDataToSheet(sheet, list, firstIndex, lastIndex);
			}
			wbook.write();
			out.flush();
		}catch (Exception e) {
			logger.error("导出Excel失败:{}",e.getMessage(),e);
			throw new CustomException("导出Excel失败");
		}finally {
			releaseResources(template, out, wbook, templateBook);
		}

    }

    /**
     * 将数据写入表格
     *
     * @param sheet      sheet
     * @param list       list
     * @param firstIndex firstIndex
     * @param lastIndex  lastIndex
     * @throws WriteException WriteException
     */
	private static void exportDataToSheet(WritableSheet sheet, List<Map<String,String>> list, int firstIndex, int lastIndex) throws WriteException {
		HashMap<Integer, Integer> widthMap = new HashMap<>();
		numFormat = numFormat==null?sheet.getCell(1, 10).getCellFormat():numFormat;
		Cell[] baseRow = sheet.getRow(6);
		int rowNo = 7;
		for (int index = firstIndex; index <= lastIndex; index++) {
			Map<String,String> item = list.get(index);
			exportData(sheet, widthMap, baseRow, rowNo, item);
			rowNo++;
		}
		setColumnWidth(sheet,widthMap);
	}

	private static void exportData(WritableSheet sheet, HashMap<Integer, Integer> widthMap, Cell[] baseRow, int rowNo,
			Map<String, String> item) throws  WriteException {
		for (int i = 0; i < baseRow.length; i++) {
			String title = baseRow[i].getContents();
			if(title==null||title.length()==0) {
				continue;
			}
			String fieldValue = item.get(title);
			fieldValue = fieldValue == null ? "" : fieldValue;
			int real = fieldValue.length()+5;
			int width = 20;
			if(widthMap.containsKey(i)) {
				width = widthMap.get(i);
				width = width>real?width:real;
			}
			width = width>real?width:real;
			widthMap.put(i, width);
			fillCell(sheet, rowNo, i, fieldValue);
		}
	}

	private static String fillCell(WritableSheet sheet, int rowNo, int i, String fieldValue) throws WriteException {
		if(i<=3) {
			Label label = new Label(i, rowNo, fieldValue);
			sheet.addCell(label);
		}else {
			if (("-").equals(fieldValue)) {
				fieldValue=fieldValue.replace(",", "").replace("-", "0");
			} else {
				fieldValue=fieldValue.replace(",", "");
			}
			double value = fieldValue.length()==0?0.0:Double.valueOf(fieldValue);
			jxl.write.Number num = new jxl.write.Number(i, rowNo, value, numFormat);
			sheet.addCell(num);
		}
		return fieldValue;
	}
	
	
	/**
	 * 设置宽自适应
	 * @param sheet
	 */
	private static void setColumnWidth(WritableSheet sheet, HashMap<Integer, Integer> widthMap) {
		int columns = sheet.getRow(9).length;
		for (int i = 0; i < columns; i++) {
			sheet.setColumnView(i, widthMap.get(i));
		}
	}

	
}
